package com.zc.baseclass.mysql.main;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * java连接MySQL数据库，并获取数据库中的数据
 * 必须先connectMysql，(之后创建数据库或者创建表或者进行表操作)，最后disconnectMysql
 * @author zhangchao
 * @date 2018-09-18
 */
public class MysqlBase {

	private final String driver = "com.mysql.jdbc.Driver";
	private Connection conn = null; //声明Connection对象
	// 其中int executeUpdate(String sql)执行sql语句（INSERT、UPDATE、DELETE）或者只要没有返回值的sql语句
	// ResultSet executeQuery(String sql)执行sql语句(SELECT)
	private Statement state = null; //创建statement类对象，用来执行SQL语句！！
	private PreparedStatement ps = null; //用来执行SQL语句！！
	private ResultSet rs = null; //ResultSet类，用来存放获取的结果集！！
	private String ip = "localhost"; //要连接的主机IP，本地连接localhost
	private int port = 3306;  //连接的端口号，默认3306
	private String user = ""; // MySQL配置时的用户名
	private String password = ""; //MySQL配置时的密码
	private String database = ""; //要连接的数据库名称
	private String table = ""; //表名
	private String sql = ""; //要执行的SQL语句
	
	public MysqlBase() {
		
	}
	
	public String getDriver() {
		return driver;
	}

	public ResultSet getRs() {
		return rs;
	}

	public String getIp() {
		return ip;
	}

	public void setIp(String ip) {
		this.ip = ip;
	}

	public int getPort() {
		return port;
	}

	public void setPort(int port) {
		this.port = port;
	}

	public String getUser() {
		return user;
	}

	public void setUser(String user) {
		this.user = user;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public String getDatabase() {
		return database;
	}

	public void setDatabase(String database) {
		this.database = database;
	}

	public String getTable() {
		return table;
	}

	public void setTable(String table) {
		this.table = table;
	}

	public String getSql() {
		return sql;
	}

	public void setSql(String sql) {
		this.sql = sql;
	}

	/*
	 * java连接MySQL，并获取数据，建立连接后，最后一定要调用 释放连接：
	 * 1.工具：eclipse, MySQL5.6, MySQL连接驱动：mysql-connector-java-5.1.27.jar
	 * 2.加载驱动：　1)在工程目录中创建lib文件夹，将下载好的JDBC放到该文件夹下;
	 *    2)右键工程名，在java build path中的Libraries分页中选择Add JARs...，选择刚才添加的JDBC。
	 * 3. 知道MySQL登录名和密码；数据库以及表。
	 * @author zhangchao
	 * @date 2018-8-2 15:55
	 * @param user:MySQL配置时的用户名
	 * @param password:MySQL配置时的密码
	 * @param database:要连接的数据库名称
	 */
	public void connectMysql(String user, String password, String database) {
		//没有ip，使用本地连接localhost；没有端口号，使用默认端口号3306
		this.connectMysql("localhost",3306,user, password,database);
	}
	
	/* @date 2018-8-2 15:50
	 * @param ip:要连接的主机IP，本地连接localhost
	 * @param user:MySQL配置时的用户名
	 * @param password:MySQL配置时的密码
	 * @param database:要连接的数据库名称
	 */
	public void connectMysql(String ip,String user, String password, String database) {
		//没有端口号，使用默认端口号3306
		this.connectMysql(ip,3306,user, password,database);
	}	
	
	/* @date 2018-8-2 15:00
	 * @param ip:要连接的主机IP，本地连接localhost
	 * @param user:MySQL配置时的用户名
	 * @param password:MySQL配置时的密码
	 * @param database:要连接的数据库名称
	 */
	public void connectMysql(int port,String user, String password, String database) {
		//没有端口号，使用默认端口号3306
		this.connectMysql("localhost",port,user, password,database);
	}
	
	/* 
	 * @date 2018-8-2 14:30
	 * @param ip:要连接的主机IP，本地连接localhost
	 * @param port:连接的端口号，默认3306
	 * @param user:MySQL配置时的用户名
	 * @param password:MySQL配置时的密码
	 * @param database:要连接的数据库名称
	 */
	public void connectMysql(String ip,int port,String user,String password, String database) {
		this.setIp(ip);
		this.setPort(port);
		this.setUser(user);
		this.setPassword(password);
		this.setDatabase(database);
		//驱动程序名
		String driver = this.getDriver();//"com.mysql.jdbc.Driver";
		//URL指向要访问的数据库名
		String url = "jdbc:mysql://" + ip + ":" + String.valueOf(port) + "/" + database;
		// jdbc:mysql://localhost:3306/database
		//System.out.println(url);
		
		//遍历查询结果集
		try {
		    //加载驱动程序
			Class.forName(driver);
			//1.getConnection()方法，连接MySQL数据库！！
			conn = DriverManager.getConnection(url,user,password); //连接数据库时会给conn赋值
			if(!conn.isClosed())
				System.out.println("成功建立数据库\""+ database +"\"连接！！");
			/*内容*/
		} catch(ClassNotFoundException e) {   
			//数据库驱动类异常处理
			System.out.println("Sorry,can't find the Driver!");   
			e.printStackTrace();   
		} catch(SQLException e) {
			//数据库连接失败异常处理
			e.printStackTrace();  
		}catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/* 
	 * Java关闭MySQL数据库连接
	 * @date 2018-09-18
	 */
	public void disconnectMysql() {
		try {
			if(rs != null)
				rs.close();
			if(state != null)
				state.close();
			if(conn != null) {
				conn.close();
				//System.out.println("断开数据库连接！");
			}
		} catch (SQLException e) {
			// 数据库连接失败异常处理
			e.printStackTrace();
		}catch (Exception e) {
			e.printStackTrace();
		}finally{
			System.out.println("成功关闭数据库\"" + this.getDatabase() + "\"连接！！");
		}
	}
	
	/**
	 * 创建数据库并建立连接
	 * @param database 数据库名
	 * @param sql 创建数据库sql语句
	 */
	public void createDatabase(String database, String sql) {
		// sql = "create database hello"
		// 一开始必须填一个已经存在的数据库
		
		Statement state;
		try {
			state = conn.createStatement();
			// 创建数据库
			state.executeUpdate(sql);
			System.out.println("成功创建数据库\""+database+"\"！");
			this.setDatabase(database);
			//System.out.println("创建数据库失败！");
			
			// 关闭原来的数据库，并打开新创建的数据库
			state.close();
			conn.close();
			//URL指向要访问的数据库名
			String url = "jdbc:mysql://" + ip + ":" + String.valueOf(port) + "/" + database;
			//url = "jdbc:mysql://localhost:3306/hello?useUnicode=true&characterEncoding=utf-8";
			conn = DriverManager.getConnection(url, user, password);
			if(!conn.isClosed())
				System.out.println("成功建立数据库\""+ database +"\"连接！！");
			/*内容*/
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 删除数据库
	 * @param database 数据库名
	 * @param sql 删除数据库的sql语句
	 */
	public void dropDatabase(String database, String sql) {
		// sql = "drop database databasename"
		// 先连接已有的数据库
		
		Statement state;
		try {
			state = conn.createStatement();
			// 创建数据库
			state.executeUpdate(sql);
			System.out.println("成功删除数据库\""+database+"\"！");
			
			state.close();
			//conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 创建表
	 * @param table 表明 
	 * @param sql 创建表的sql语句
	 */
	public void createTable(String table, String sql) {
		// sql = "create table pepole(name varchar(10) not null, age int(4) not null)charset=utf8;"
		Statement state = null;
		try {
			//执行创建表
			state = conn.createStatement();
			state.executeUpdate(sql);
			System.out.println("成功创建表\""+table+"\"！");
			this.setTable(table);
			
			state.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 指定数据库创建表
	 * @param database 数据库
	 * @param table 表
	 * @param sql 创建表语句
	 */
	public void createTable(String database, String table, String sql) {
		// sql = "create table pepole(name varchar(10) not null, age int(4) not null)charset=utf8;"
		disconnectMysql();
		connectMysql(ip, port, user, password, database);
		Statement state = null;
		try {
			//执行创建表
			state = conn.createStatement();
			state.executeUpdate(sql);
			System.out.println("成功创建表\""+table+"\"！");
			this.setTable(table);
			
			state.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 删除表
	 * @param table 表名
	 * @param sql 删除表的语句
	 */
	public void dropTable(String table, String sql) {
		// sql = "drop table tablename";
		Statement state = null;
		try {
			//执行创建表
			state = conn.createStatement();
			state.executeUpdate(sql);
			System.out.println("成功删除表\""+table+"\"！");
			this.setTable(table);
			
			state.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 增
	 * @param table 表
	 * @param sql 表中增加元素的sql语句
	 */
	public void insert(String table, String sql) {
		//String sql = "insert into " + table + " values()";
		Statement state;
        try {
        	state = conn.createStatement();   //容器
        	//String sql="insert into xs values('1108','张伟','汉企')";   //SQL语句
			state.executeUpdate(sql); //将sql语句上传至数据库执行
			
			state.close(); //
		} catch (SQLException e) {
			e.printStackTrace();
		}         
	}
	
	/**
	 * 删
	 * @param table 表
	 * @param sql
	 */
	public void delete(String table, String sql) {
		Statement state;
		try {
			state = conn.createStatement();
			//String sql="delete from xs where xuehao='1108'";   //SQL语句
	        state.executeUpdate(sql);         //将sql语句上传至数据库执行
	        
	        state.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}   //容器  
	}
	
	/**
	 * 改
	 * @param table 表
	 * @param sql
	 */
	public void update(String table, String sql) {
		Statement state;
		try {
			state = conn.createStatement(); //容器
			//String sql="update xs set xuexiao='淄博汉企' where xuehao='1101' ";   //SQL语句
	        state.executeUpdate(sql);         //将sql语句上传至数据库执行
	        
	        state.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}   
	}
	
	/**
	 * 查
	 * @param table 表
	 * @param sql
	 */
	public ResultSet select(String table, String sql) {
		// 2.创建statement类对象，用来执行SQL语句！！
		//Statement state;
		try {
			// 查找表中数据时会给state赋值
			state = conn.createStatement();
			// 要执行的SQL语句，其中employee是mysqltest数据库中的表
			// String sql = "select * from employee";
			// 3.ResultSet类，用来存放获取的结果集！！
			//ResultSet rs = state.executeQuery(sql);
			rs = state.executeQuery(sql); // 查找表中数据时会给rs赋值

			// 获取表结构
        	//ResultSetMetaData metaData =  rs.getMetaData();  
        	// 得到列的总数
			//int columnCount= metaData.getColumnCount();
			// 得到行的总数
			//rs.last();
			//int row = rs.getRow();
			//System.out.println("col = " + columnCount + ", row = " + row);
			//while (rs.next()) {}
			
			//rs.close();
			//state.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return rs;
	}
	
	/**
	 * 查
	 * @param table 表
	 * @param sql
	 * @return 数据
	 */
	public List<List<Object>> selectList(String table, String sql) {
		// 2.创建statement类对象，用来执行SQL语句！！
		Statement state;
		List<List<Object>> list = new ArrayList<List<Object>>();
		try {
			state = conn.createStatement();
			// 要执行的SQL语句，其中employee是mysqltest数据库中的表
			// String sql = "select * from employee";
			// 3.ResultSet类，用来存放获取的结果集！！
			ResultSet rs = state.executeQuery(sql);
			rs = state.executeQuery(sql);

			// 获取表结构
        	ResultSetMetaData metaData =  rs.getMetaData();  
        	// 得到列的总数
			int columnCount= metaData.getColumnCount();
			
			while (rs.next()) {
				List<Object> lst = new ArrayList<Object>();
                for (int i = 1; i <= columnCount; ++i) {
                	// 每一行的数据
                    lst.add(rs.getObject(i) == null ? "" : rs.getObject(i));
                    //System.out.print(metaData.getColumnLabel(i)+":"+rs.getObject(i)+" ");
                }
                //System.out.println();
                list.add(lst); //增加一列
			}
			
			// 得到行的总数
			// rs.last();
			// int row = rs.getRow();
			// System.out.println("col = " + columnCount + ", row = " + row);
			
			rs.close();
			state.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	public void printSelectList(String table, String sql) {
		// String sql = "select * from employee"
		List<List<Object>> list = selectList(table, sql);
		for(List<Object> list2 : list) {
			for(Object obj : list2) {
				System.out.print(obj + " ");
			}
			System.out.println();
		}
		/*
		for(int i=0; i<list.size(); i++) {
			List<Object> list2 = list.get(i);
			for(int j=0; j<list2.size();j++) {
				System.out.print(list2.get(j) + " ");
			}
			System.out.println();
		}
		*/
	}
	
	/**
	 * 查
	 * @param table 表
	 * @param sql
	 * @return 表数据
	 */
	public List<Map<String,Object>> selectMap(String table, String sql) {
		// 2.创建statement类对象，用来执行SQL语句！！
		Statement state;
		List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
		try {
			state = conn.createStatement();
			// 要执行的SQL语句，其中employee是mysqltest数据库中的表
			// String sql = "select * from employee";
			// 3.ResultSet类，用来存放获取的结果集！！
			ResultSet rs = state.executeQuery(sql);
			rs = state.executeQuery(sql);

			// 获取表结构
        	ResultSetMetaData metaData =  rs.getMetaData();  
        	// 得到列的总数
			int columnCount= metaData.getColumnCount();
			
			while (rs.next()) {
				Map<String,Object> lst = new HashMap<String,Object>();
                for (int i = 1; i <= columnCount; ++i) {
                	// 每一行的 列名:数据
                    lst.put(metaData.getColumnLabel(i) == null ?  "" : metaData.getColumnLabel(i),rs.getObject(i) == null ? "" : rs.getObject(i));
                }
                list.add(lst); //增加一列
			}
			// 得到行的总数
			// rs.last();
			// int row = rs.getRow();
			// System.out.println("col = " + columnCount + ", row = " + row);
			rs.close();
			state.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}
	
	public void printSelectMap(String table, String sql) {
		// String sql = "select * from employee"
		List<Map<String,Object>> list = selectMap(table, sql);
		for(Map<String,Object> map : list) {
			for(String str : map.keySet()) {
				System.out.print(str+":"+map.get(str) + " ");
			}
			System.out.println();
		}
		/*
		for(int i=0; i<list.size(); i++) {
			Map<String,Object> map = list.get(i);
			for(String str : map.keySet()) {
				System.out.print(str+":"+map.get(str) + " ");
			}
			System.out.println();
		}
		*/
	}
	
	/*
	 * 获取表数据，对表数据进行处理
	 * @date 2018-8-2 16:50
	 */
	public String processTableData(ResultSet rs) {
		// Demo
		String str = processTableDataDemo(rs);
		return str;
	}
	
	/*
	 * processTableData处理的一个Demo
	 * @date 2018-08-02 17:02
	 */
	private String processTableDataDemo(ResultSet rs) {
		// 显示表中数据
		String str = "";
		try {
			// 获取表结构
        	ResultSetMetaData metaData =  rs.getMetaData();  
        	// 得到列的总数
			int columnCount= metaData.getColumnCount();
			while(rs.next()){
				for(int i=1; i<=columnCount; i++) {
					// 添加键值对，比如说{name:Wp}通过name找到wp
	            	String columnName = metaData.getColumnLabel(i); 
	            	String value = String.valueOf(rs.getObject(columnName));
	            	if(i < columnCount)
	            		str += columnName + ":" + value + ",";
	            	else 
	            		str += columnName + ":" + value;
	            	//System.out.print(columnName + ":" + value + " ");
				}
				str += "\n";
				//System.out.println();
			}
		} catch (SQLException e) {
			// 数据库连接失败异常处理
			e.printStackTrace();
		}catch (Exception e) {
			e.printStackTrace();
		}
		return str;
	}

}
